package org.password.utils;


import org.password.entity.PasswordInfo;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class DBUtil {

    private static final String JDBC_DRIVER = "org.sqlite.JDBC";


    private static final String DB_URL = "jdbc:sqlite:D:\\password.db";

    public Connection getConn() {
        Connection connection = null;
        try {

            Class.forName(JDBC_DRIVER);

            File file = new File("D:\\password.db");
            if (!file.exists()) {
                file.createNewFile();
                connection = DriverManager.getConnection(DB_URL);
                Statement statement = connection.createStatement();
                statement.execute(" CREATE TABLE password_info (\n" +
                        "       id TEXT PRIMARY KEY,\n" +
                        "       website TEXT NOT NULL,\n" +
                        "       account TEXT NOT NULL,\n" +
                        "       password TEXT NOT NULL\n" +
                        "   );");
                connection.commit();
            }

            connection = DriverManager.getConnection(DB_URL);
        } catch (ClassNotFoundException | SQLException | IOException e) {
            System.out.println("未能加载SQLite JDBC驱动！");
            e.printStackTrace();
        }
        return connection;
    }

    public List<PasswordInfo> getData() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        ArrayList<PasswordInfo> passwordInfos = new ArrayList<>();
        try {
            conn = getConn();
            statement = conn.createStatement();
            String sql = "select * from password_info";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String website = resultSet.getString("website");
                String account = resultSet.getString("account");
                String password = resultSet.getString("password");
                String id = resultSet.getString("id");
                PasswordInfo passwordInfo = new PasswordInfo(id, website, account, password);
                passwordInfos.add(passwordInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return passwordInfos;
    }

    public Boolean insertData(String website, String account, String password) {
        Connection conn = null;
        int i = 0;
        try {
            String id = UUID.randomUUID().toString();
            conn = getConn();
            Statement statement = conn.createStatement();
            String sql = "INSERT INTO password_info (id,website, account, password)\n" +
                    "VALUES ('" + id + "', '" + website + "', '" + account + "', '" + password + "')";
            i = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return i != 0;
    }

    public boolean delPassword(List<String> ids) {
        Connection conn = null;
        int i = 0;
        try {
            StringBuilder sb = new StringBuilder();

            for (String id : ids) {
                sb.append("'").append(id).append("',");
            }
            String substring = sb.substring(0, sb.length() - 1);
            conn = getConn();
            Statement statement = conn.createStatement();
            String sql = "DELETE FROM password_info\n" +
                    "WHERE id in (" + substring + ")";
            i = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i != 0;
    }

}
